-- 4G信号采集数据库表结构
-- 数据库: 4GSignalCollection
-- 服务器: 127.0.0.1:3307 (SQL Server)

-- 创建4G信号采集数据表
CREATE TABLE [dbo].[signal_collections] (
    [id] BIGINT IDENTITY(1,1) PRIMARY KEY,
    [device_id] NVARCHAR(50) NOT NULL,                    -- 设备ID
    [signal_strength] INT NOT NULL,                       -- 4G信号强度 (dBm)
    [latitude] DECIMAL(10, 8) NOT NULL,                   -- GPS纬度
    [longitude] DECIMAL(11, 8) NOT NULL,                  -- GPS经度
    [altitude] DECIMAL(8, 2) NULL,                        -- GPS海拔 (米)
    [accuracy] DECIMAL(8, 2) NULL,                        -- GPS精度 (米)
    [network_type] NVARCHAR(20) NULL,                     -- 网络类型 (4G/LTE/5G)
    [operator_name] NVARCHAR(100) NULL,                   -- 运营商名称
    [cell_id] NVARCHAR(50) NULL,                          -- 基站ID
    [lac] NVARCHAR(20) NULL,                              -- 位置区域码
    [mcc] NVARCHAR(10) NULL,                              -- 移动国家码
    [mnc] NVARCHAR(10) NULL,                              -- 移动网络码
    [rsrp] INT NULL,                                      -- RSRP值 (dBm)
    [rsrq] DECIMAL(5, 2) NULL,                            -- RSRQ值 (dB)
    [rssi] INT NULL,                                      -- RSSI值 (dBm)
    [snr] DECIMAL(5, 2) NULL,                             -- 信噪比 (dB)
    [collected_at] DATETIME2 NOT NULL DEFAULT GETUTCDATE(), -- 采集时间
    [uploaded_at] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),  -- 上传时间
    [created_at] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),   -- 记录创建时间
    [updated_at] DATETIME2 NOT NULL DEFAULT GETUTCDATE()    -- 记录更新时间
);

-- 创建索引
CREATE INDEX [IX_signal_collections_device_id] ON [dbo].[signal_collections] ([device_id]);
CREATE INDEX [IX_signal_collections_collected_at] ON [dbo].[signal_collections] ([collected_at]);
CREATE INDEX [IX_signal_collections_location] ON [dbo].[signal_collections] ([latitude], [longitude]);
CREATE INDEX [IX_signal_collections_signal_strength] ON [dbo].[signal_collections] ([signal_strength]);

-- 创建设备信息表
CREATE TABLE [dbo].[devices] (
    [id] BIGINT IDENTITY(1,1) PRIMARY KEY,
    [device_id] NVARCHAR(50) NOT NULL UNIQUE,             -- 设备ID
    [device_name] NVARCHAR(100) NULL,                     -- 设备名称
    [device_model] NVARCHAR(100) NULL,                    -- 设备型号
    [os_version] NVARCHAR(50) NULL,                       -- 操作系统版本
    [app_version] NVARCHAR(20) NULL,                      -- APP版本
    [last_seen] DATETIME2 NULL,                           -- 最后在线时间
    [is_active] BIT NOT NULL DEFAULT 1,                   -- 是否激活
    [created_at] DATETIME2 NOT NULL DEFAULT GETUTCDATE(), -- 创建时间
    [updated_at] DATETIME2 NOT NULL DEFAULT GETUTCDATE()  -- 更新时间
);

-- 创建设备表索引
CREATE INDEX [IX_devices_device_id] ON [dbo].[devices] ([device_id]);
CREATE INDEX [IX_devices_is_active] ON [dbo].[devices] ([is_active]);

-- 创建统计视图
CREATE VIEW [dbo].[signal_statistics] AS
SELECT 
    device_id,
    COUNT(*) as total_collections,
    AVG(CAST(signal_strength AS FLOAT)) as avg_signal_strength,
    MIN(signal_strength) as min_signal_strength,
    MAX(signal_strength) as max_signal_strength,
    MIN(collected_at) as first_collection,
    MAX(collected_at) as last_collection,
    COUNT(DISTINCT CAST(collected_at AS DATE)) as collection_days
FROM [dbo].[signal_collections]
GROUP BY device_id;

-- 插入示例数据
INSERT INTO [dbo].[devices] ([device_id], [device_name], [device_model], [os_version], [app_version])
VALUES 
    ('DEV001', '测试设备1', 'Android Phone', 'Android 13', '1.0.0'),
    ('DEV002', '测试设备2', 'Android Phone', 'Android 12', '1.0.0');

-- 插入示例信号数据
INSERT INTO [dbo].[signal_collections] 
([device_id], [signal_strength], [latitude], [longitude], [altitude], [accuracy], [network_type], [operator_name], [collected_at])
VALUES 
    ('DEV001', -85, 39.9042, 116.4074, 50.0, 5.0, '4G', '中国移动', GETUTCDATE()),
    ('DEV001', -78, 39.9045, 116.4077, 52.0, 4.5, '4G', '中国移动', GETUTCDATE()),
    ('DEV002', -92, 39.9040, 116.4070, 48.0, 6.0, '4G', '中国联通', GETUTCDATE());